﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;
using Microsoft.VisualBasic;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CRMBiz
{
    public static class LoanInformationHandler
    {
        /* this statement bind  payment Peroid ddl */
        public static DataSet GetAllPaymentPeriod()
        {
            DataSet ds;
            try
            {
                ds = DBhelper.ExecuteDataset("SELECT * FROM am_paymentperiod");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /* this statement bind  Last Result ddl */
        public static DataSet GetLastResult()
        {
            DataSet ds;
            try
            {
                ds = DBhelper.ExecuteDataset("SELECT * FROM crm_lastresult");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /*This function will bind the assessment last result*/
        public static DataSet GetAssessmentLastResult()
        {
            DataSet ds;
            try
            {
                ds = DBhelper.ExecuteDataset("SELECT * FROM crm_assessmentlastresult");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static int AddLoanInfoFromAsessment(string app_id, string LoanNumber, string Lender, string CreateDate, string EditDate, string LoanStatus, string LastResult, string Memo)
        {
            int loanid = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@LoanNumber", LoanNumber),
                    DBhelper.SetParameter("@Lender", Lender),
                    DBhelper.SetParameter("@CreateDate", CreateDate),
                    DBhelper.SetParameter("@EditDate", EditDate),
                    DBhelper.SetParameter("@LoanStatus", LoanStatus),
                    DBhelper.SetParameter("@LastResult", LastResult),
                    DBhelper.SetParameter("@Memo", Memo)
                };

                string sqlInertLoanQuety = "insert into am_loan1(LoanNumber,app_id,Lender,CreateDate,EditDate,LoanStatusID,LastResult,Memo) values(@LoanNumber,@app_id,@Lender,STR_TO_DATE(@CreateDate,'%m/%d/%Y'),STR_TO_DATE(@EditDate,'%m/%d/%Y'),@LoanStatus,@LastResult,@Memo);SELECT loanid FROM `am_loan1` order by  loanid desc limit 1";
                DataTable dt = DBhelper.ExecuteDataset(sqlInertLoanQuety, para).Tables[0];
                if (dt.Rows.Count > 0)
                    loanid = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return loanid;
        }

        public static int SimpleInterestPaymentScheduleByRepayment(int LoanID, decimal repaymentAmount, string paymentperiod, DateTime firstPaymentDate)
        {
            DateTime DueDate1, settlementDate = DateTime.Now;
            decimal Repayment1, Principal1, Balance1, Interest1;
            int daysBetweenRepayments = 0;
            decimal totalInterestAmount = 0, loanAmount = 0, annumInterestRate = 0;
            int i = 0, intReturnValue = 1;
            string sqlQuery = string.Empty;
            try
            {
                var para1 = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                sqlQuery = "DELETE from am_repaymentschedule where loanid=@LoanID;SELECT LoanID,LoanAmount,Product,Type,DrawDownDate,LoanTerm,FirstPaymentDue,PaymentPeriod,InterestMethod,LowerRate FROM `am_loan1` where LoanID=@LoanID order by  LoanID desc limit 1";
                var dtdata = DBhelper.ExecuteDataset(sqlQuery, para1).Tables[0];

                if (dtdata.Rows.Count > 0)
                {
                    if (dtdata.Rows[0]["LoanAmount"].ToString() != string.Empty)
                        loanAmount = Convert.ToDecimal(dtdata.Rows[0]["LoanAmount"].ToString());
                    else
                        loanAmount = 0;
                    if (dtdata.Rows[0]["DrawDownDate"].ToString() != string.Empty)
                        settlementDate = Convert.ToDateTime(dtdata.Rows[0]["DrawDownDate"].ToString());
                    else
                        settlementDate = DateTime.Now;
                    if (dtdata.Rows[0]["LowerRate"].ToString() != string.Empty)
                        annumInterestRate = Convert.ToDecimal(dtdata.Rows[0]["LowerRate"].ToString());
                    else
                        annumInterestRate = 0;
                }

                if (paymentperiod == "Weekly")
                    daysBetweenRepayments = 7;
                else if (paymentperiod == "Daily(Mon-Fri)")
                    daysBetweenRepayments = 1;
                if (paymentperiod == "Fortnightly")
                    daysBetweenRepayments = 14;
                if (paymentperiod == "Monthly")
                    daysBetweenRepayments = 30;

                int daysFirstPeriod = (firstPaymentDate - settlementDate).Days;
                decimal dailyInterestRate = annumInterestRate / 365;
                decimal dailyInterestAmount = loanAmount * dailyInterestRate;
                decimal balance = loanAmount;

                while (balance > 0)
                {
                    DueDate1 = firstPaymentDate.AddDays(i * daysBetweenRepayments);
                    Repayment1 = repaymentAmount;
                    decimal interestAmount;
                    if (i == 0)
                        interestAmount = dailyInterestAmount * daysFirstPeriod;
                    else
                        interestAmount = dailyInterestAmount * daysBetweenRepayments;
                    Interest1 = interestAmount;
                    decimal principal = repaymentAmount - interestAmount;
                    Principal1 = principal;
                    if (balance - principal > 0)
                        balance = balance - principal;
                    else
                    {
                        principal = balance;
                        Principal1 = principal;
                        Repayment1 = principal + interestAmount;
                        balance = 0;
                    }
                    Balance1 = balance;
                    totalInterestAmount += interestAmount;
                    i++;
                    var para = new[] {
                        DBhelper.SetParameter("@LoanID", LoanID),
                        DBhelper.SetParameter("@Loandate", DateTime.Parse(DueDate1.ToString()).ToShortDateString()),
                        DBhelper.SetParameter("@Daily", Math.Round((decimal.Parse(Repayment1.ToString())), 2)),
                        DBhelper.SetParameter("@Balance", Math.Round((decimal.Parse(Balance1.ToString())), 2))
                    };
                    sqlQuery = "insert into am_repaymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int AddPaymentSchedule(int LoanID, string paraPurpose, string paraProduct, string paraType, string paraSettlementDate,
                                             int paraTerm, string paraFirstPaymentDate, string paraPaymetFrequecy, decimal paraAnnumInterestRate,
                                             string paraInterestMethod, decimal paraLoanAmount)
        {
            DateTime DueDate1;
            int daysFirstPeriod, daysBetweenRepayments = 0, intReturnValue = 0;
            decimal dailyInterestRate, dailyInterestAmount, Repayment1, Balance1;
            string sqlQuery = string.Empty;
            try
            {
                var para1 = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                sqlQuery = "DELETE from am_paymentschedule where loanid=@LoanID;SELECT LoanID,LoanAmount,Product,Type,DrawDownDate,LoanTerm,FirstPaymentDue,PaymentPeriod,InterestMethod,LowerRate FROM `am_loan1` where LoanID=@LoanID order by  LoanID desc limit 1";
                var dtdata = DBhelper.ExecuteDataset(sqlQuery, para1).Tables[0];

                DateTime FirstPaymentDate = Convert.ToDateTime(paraFirstPaymentDate);
                DateTime SettlementDate = Convert.ToDateTime(paraSettlementDate);

                if (paraLoanAmount > 0)
                {
                    #region Simple
                    if (paraInterestMethod == "Simple")
                    {
                        if (paraProduct == "Unsecured Business Loan")
                        {
                            if (paraType == "P And I")
                            {
                                if (paraPaymetFrequecy == "Weekly")
                                {
                                    daysBetweenRepayments = 7;
                                    daysFirstPeriod = (FirstPaymentDate - SettlementDate).Days;
                                    dailyInterestRate = paraAnnumInterestRate / 365;
                                    dailyInterestAmount = paraLoanAmount * dailyInterestRate;
                                    dailyInterestAmount = dailyInterestAmount * 7;

                                    for (int i = 0; paraLoanAmount > 0; i++)
                                    {

                                        DueDate1 = SettlementDate.AddDays(i * daysBetweenRepayments);
                                        if (dailyInterestAmount < paraLoanAmount)
                                            Repayment1 = dailyInterestAmount;
                                        else
                                            Repayment1 = paraLoanAmount;
                                        Balance1 = paraLoanAmount;
                                        paraLoanAmount = paraLoanAmount - dailyInterestAmount;
                                        var para = new[] {
                                        DBhelper.SetParameter("@LoanID", LoanID),
                                        DBhelper.SetParameter("@Loandate", DateTime.Parse(DueDate1.ToString()).ToShortDateString()),
                                        DBhelper.SetParameter("@Daily", Math.Round((decimal.Parse(Repayment1.ToString())), 2)),
                                        DBhelper.SetParameter("@Balance", Math.Round((decimal.Parse(Balance1.ToString())), 2))
                                        };
                                        sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                        DBhelper.ExecuteNonQuery(sqlQuery, para);
                                    }
                                    intReturnValue = 1;
                                }
                                if (paraPaymetFrequecy == "Fortnightly")
                                {
                                    daysBetweenRepayments = 14;
                                    daysFirstPeriod = (FirstPaymentDate - SettlementDate).Days;
                                    dailyInterestRate = paraAnnumInterestRate / 365;
                                    dailyInterestAmount = paraLoanAmount * dailyInterestRate;
                                    dailyInterestAmount = dailyInterestAmount * 14;

                                    for (int i = 0; i < paraLoanAmount; i++)
                                    {
                                        DueDate1 = SettlementDate.AddDays(i * daysBetweenRepayments);
                                        if (dailyInterestAmount < paraLoanAmount)
                                            Repayment1 = dailyInterestAmount;
                                        else
                                            Repayment1 = paraLoanAmount;
                                        Balance1 = paraLoanAmount;
                                        paraLoanAmount = paraLoanAmount - dailyInterestAmount;
                                        var para = new[] {
                                        DBhelper.SetParameter("@LoanID", LoanID),
                                        DBhelper.SetParameter("@Loandate", DateTime.Parse(DueDate1.ToString()).ToShortDateString()),
                                        DBhelper.SetParameter("@Daily", Math.Round((decimal.Parse(Repayment1.ToString())), 2)),
                                        DBhelper.SetParameter("@Balance", Math.Round((decimal.Parse(Balance1.ToString())), 2))
                                        };
                                        sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                        DBhelper.ExecuteNonQuery(sqlQuery, para);
                                    }
                                    intReturnValue = 1;
                                }
                                if (paraPaymetFrequecy == "Monthly")
                                {
                                    daysBetweenRepayments = 30;
                                    daysFirstPeriod = (FirstPaymentDate - SettlementDate).Days;
                                    dailyInterestRate = paraAnnumInterestRate / 365;
                                    dailyInterestAmount = paraLoanAmount * dailyInterestRate;
                                    dailyInterestAmount = dailyInterestAmount * 30;

                                    for (int i = 0; i < paraLoanAmount; i++)
                                    {
                                        DueDate1 = SettlementDate.AddDays(i * daysBetweenRepayments);
                                        if (dailyInterestAmount < paraLoanAmount)
                                            Repayment1 = dailyInterestAmount;
                                        else
                                            Repayment1 = paraLoanAmount;
                                        Balance1 = paraLoanAmount;
                                        paraLoanAmount = paraLoanAmount - dailyInterestAmount;
                                        var para = new[] {
                                        DBhelper.SetParameter("@LoanID", LoanID),
                                        DBhelper.SetParameter("@Loandate", DateTime.Parse(DueDate1.ToString()).ToShortDateString()),
                                        DBhelper.SetParameter("@Daily", Math.Round((decimal.Parse(Repayment1.ToString())), 2)),
                                        DBhelper.SetParameter("@Balance", Math.Round((decimal.Parse(Balance1.ToString())), 2))
                                        };
                                        sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                        DBhelper.ExecuteNonQuery(sqlQuery, para);
                                    }
                                    intReturnValue = 1;
                                }
                                if (paraPaymetFrequecy == "Daily(Mon-Fri)")
                                {
                                    daysBetweenRepayments = 1;
                                    daysFirstPeriod = (FirstPaymentDate - SettlementDate).Days;
                                    dailyInterestRate = paraAnnumInterestRate / 365;
                                    dailyInterestAmount = paraLoanAmount * dailyInterestRate;

                                    for (int i = 0; i < paraLoanAmount; i++)
                                    {

                                        if (SettlementDate.DayOfWeek.ToString() != "Saturday" && SettlementDate.DayOfWeek.ToString() != "Sunday")
                                        {
                                            DueDate1 = SettlementDate;
                                            if (dailyInterestAmount < paraLoanAmount)
                                                Repayment1 = dailyInterestAmount;
                                            else
                                                Repayment1 = paraLoanAmount;
                                            Balance1 = paraLoanAmount;
                                            paraLoanAmount = paraLoanAmount - dailyInterestAmount;
                                            var para = new[] {
                                            DBhelper.SetParameter("@LoanID", LoanID),
                                            DBhelper.SetParameter("@Loandate", DateTime.Parse(DueDate1.ToString()).ToShortDateString()),
                                            DBhelper.SetParameter("@Daily", Math.Round((decimal.Parse(Repayment1.ToString())), 2)),
                                            DBhelper.SetParameter("@Balance", Math.Round((decimal.Parse(Balance1.ToString())), 2))
                                            };
                                            sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                            DBhelper.ExecuteNonQuery(sqlQuery, para);
                                        }
                                        else
                                        {
                                            i = i - 1;
                                        }
                                        SettlementDate = SettlementDate.AddDays(1);
                                    }

                                    intReturnValue = 1;
                                }
                            }
                        }
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int EditPaymentSchedule(int LoanID, string EditRepayment, string EditFreq, string EditFirstPaymentDue)
        {
            /* for   payment schedule */
            DateTime FirstPaymentDue, FirstPaymentDue1, DrawDownDate;
            string PaymentPeriod = string.Empty, sqlQuery = string.Empty, InterestMethod = string.Empty, Getalldate = string.Empty, PayNumber = string.Empty, Facility = string.Empty, RepaymentType = string.Empty;
            decimal RepaymentAmount, LoanAmount, DailyPayment, TotalPayment, DailyInterest, TotalInterest, LowerRate, DailyRate = 0;
            int Days, intReturnValue = 0;
            decimal LoanTerm;
            try
            {
                var para1 = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                sqlQuery = "DELETE from am_repaymentschedule where loanid=@LoanID;SELECT LoanID,LoanAmount,Product,Type,DrawDownDate,LoanTerm,FirstPaymentDue,PaymentPeriod,InterestMethod,LowerRate FROM `am_loan1` where LoanID=@LoanID order by  LoanID desc limit 1";
                var dtdata = DBhelper.ExecuteDataset(sqlQuery, para1).Tables[0];
                if (dtdata.Rows.Count > 0)
                {
                    Facility = dtdata.Rows[0]["Product"].ToString();
                    RepaymentType = dtdata.Rows[0]["Type"].ToString();
                    InterestMethod = dtdata.Rows[0]["InterestMethod"].ToString();
                    if (dtdata.Rows[0]["LoanAmount"].ToString() != "")
                    {
                        LoanAmount = Convert.ToDecimal(dtdata.Rows[0]["LoanAmount"].ToString());
                        FirstPaymentDue = Convert.ToDateTime(EditFirstPaymentDue);
                        FirstPaymentDue1 = Convert.ToDateTime(EditFirstPaymentDue);
                        DrawDownDate = Convert.ToDateTime(dtdata.Rows[0]["DrawDownDate"].ToString());
                        PaymentPeriod = EditFreq;
                        LowerRate = Convert.ToDecimal(dtdata.Rows[0]["LowerRate"].ToString());
                        string[] words = dtdata.Rows[0]["LoanTerm"].ToString().Split(' ');
                        LoanTerm = Convert.ToInt32(words[0]);
                        RepaymentAmount = Convert.ToDecimal(EditRepayment);

                        #region Simple
                        if (InterestMethod == "Simple")
                        {
                            if (Facility == "Unsecured Business Loan")
                            {
                                if (RepaymentType == "P And I")
                                {
                                    if (PaymentPeriod == "Weekly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 7);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = Convert.ToDecimal(EditRepayment);
                                        for (decimal i = TotalPayment; i >= 0; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                TotalPayment = TotalPayment - DailyPayment;

                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(7);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Fortnightly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 14);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = Convert.ToDecimal(EditRepayment);
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                TotalPayment = (TotalPayment - DailyPayment);
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(14);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Monthly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 30);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = Convert.ToDecimal(EditRepayment);
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                TotalPayment = (TotalPayment - DailyPayment);
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(30);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Daily(Mon-Fri)")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 1);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = Convert.ToDecimal(EditRepayment);
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (FirstPaymentDue.DayOfWeek.ToString() != "Saturday" && FirstPaymentDue.DayOfWeek.ToString() != "Sunday")
                                            {
                                                if (TotalPayment > DailyPayment)
                                                {
                                                    TotalPayment = (TotalPayment - DailyPayment);
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                                else
                                                {
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                            }
                                            else
                                            {
                                                i = i - 1;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(1);
                                        }
                                        intReturnValue = 1;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int AddSinglePayment(int LoanID, string SingleRepayment, string SingleFirstPaymentDue)
        {
            /* for payment schedule */
            DateTime FirstPaymentDue, FirstPaymentDue1, DrawDownDate, AddSingleFirstPaymentDue;
            string PaymentPeriod = string.Empty, sqlQuery = string.Empty, InterestMethod = string.Empty, Getalldate = string.Empty, PayNumber = string.Empty, Facility = string.Empty, RepaymentType = string.Empty;
            decimal LoanAmount, DailyPayment = 0, TotalPayment = 0, DailyInterest, TotalInterest, LowerRate, DailyRate = 0, AddSingleRepayment;
            int Days, intReturnValue = 0;
            decimal LoanTerm;
            try
            {
                var para1 = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                sqlQuery = "DELETE from am_repaymentschedule where loanid=@LoanID;SELECT LoanID,LoanAmount,Product,Type,DrawDownDate,LoanTerm,FirstPaymentDue,PaymentPeriod,InterestMethod,LowerRate FROM `am_loan1` where LoanID=@LoanID order by  LoanID desc limit 1";
                var dtdata = DBhelper.ExecuteDataset(sqlQuery, para1).Tables[0];
                if (dtdata.Rows.Count > 0)
                {
                    Facility = dtdata.Rows[0]["Product"].ToString();
                    RepaymentType = dtdata.Rows[0]["Type"].ToString();
                    InterestMethod = dtdata.Rows[0]["InterestMethod"].ToString();
                    if (dtdata.Rows[0]["LoanAmount"].ToString() != "")
                    {
                        LoanAmount = Convert.ToDecimal(dtdata.Rows[0]["LoanAmount"].ToString());
                        FirstPaymentDue = Convert.ToDateTime("1/7/2013 12:00:00 AM");
                        FirstPaymentDue1 = Convert.ToDateTime(dtdata.Rows[0]["FirstPaymentDue"].ToString());
                        DrawDownDate = Convert.ToDateTime(dtdata.Rows[0]["DrawDownDate"].ToString());
                        PaymentPeriod = "Weekly";
                        LowerRate = Convert.ToDecimal(dtdata.Rows[0]["LowerRate"].ToString());
                        string[] words = dtdata.Rows[0]["LoanTerm"].ToString().Split(' ');
                        LoanTerm = Convert.ToInt32(words[0]);
                        AddSingleRepayment = Convert.ToDecimal(SingleRepayment);
                        AddSingleFirstPaymentDue = Convert.ToDateTime(SingleFirstPaymentDue);

                        #region Simple
                        if (InterestMethod == "Simple")
                        {
                            if (Facility == "Unsecured Business Loan")
                            {
                                if (RepaymentType == "P And I")
                                {
                                    if (PaymentPeriod == "Weekly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 7);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = TotalPayment / LoanTerm;

                                        for (decimal i = TotalPayment; i >= 0; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                if (i == TotalPayment)
                                                {
                                                    TotalPayment = TotalPayment - AddSingleRepayment;
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", AddSingleFirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((AddSingleRepayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                                else
                                                {
                                                    TotalPayment = TotalPayment - DailyPayment;

                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(7);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Fortnightly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 14);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = TotalPayment / LoanTerm;
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                if (i == TotalPayment)
                                                {
                                                    TotalPayment = TotalPayment - AddSingleRepayment;
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", AddSingleFirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((AddSingleRepayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                                else
                                                {
                                                    TotalPayment = (TotalPayment - DailyPayment);
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(14);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Monthly")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 30);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = TotalPayment / LoanTerm;
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (TotalPayment > DailyPayment)
                                            {
                                                if (i == TotalPayment)
                                                {
                                                    TotalPayment = TotalPayment - AddSingleRepayment;
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate", AddSingleFirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((AddSingleRepayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                                else
                                                {
                                                    TotalPayment = (TotalPayment - DailyPayment);
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                            }
                                            else
                                            {
                                                var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                break;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(30);
                                        }
                                        intReturnValue = 1;
                                    }
                                    if (PaymentPeriod == "Daily(Mon-Fri)")
                                    {
                                        DailyRate = ((LowerRate / 100) / 365);
                                        DailyInterest = (LoanAmount * DailyRate);
                                        Days = Convert.ToInt32(LoanTerm * 1);
                                        TotalInterest = DailyInterest * Days;
                                        TotalPayment = LoanAmount + TotalInterest;
                                        DailyPayment = TotalPayment / LoanTerm;
                                        for (int i = 0; i <= LoanTerm; i++)
                                        {
                                            if (FirstPaymentDue.DayOfWeek.ToString() != "Saturday" && FirstPaymentDue.DayOfWeek.ToString() != "Sunday")
                                            {
                                                if (TotalPayment > DailyPayment)
                                                {
                                                    if (i == TotalPayment)
                                                    {
                                                        TotalPayment = TotalPayment - AddSingleRepayment;
                                                        var para = new[] {
                                                        DBhelper.SetParameter("@LoanID", LoanID),
                                                        DBhelper.SetParameter("@Loandate", AddSingleFirstPaymentDue.ToShortDateString()),
                                                        DBhelper.SetParameter("@Daily", Math.Round((AddSingleRepayment), 2)),
                                                        DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                        };
                                                        sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                        DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                    }
                                                    else
                                                    {
                                                        TotalPayment = (TotalPayment - DailyPayment);
                                                        var para = new[] {
                                                        DBhelper.SetParameter("@LoanID", LoanID),
                                                        DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                        DBhelper.SetParameter("@Daily", Math.Round((DailyPayment), 2)),
                                                        DBhelper.SetParameter("@Balance", Math.Round(TotalPayment, 2))
                                                        };
                                                        sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                        DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                    }
                                                }
                                                else
                                                {
                                                    var para = new[] {
                                                    DBhelper.SetParameter("@LoanID", LoanID),
                                                    DBhelper.SetParameter("@Loandate",  FirstPaymentDue.ToShortDateString()),
                                                    DBhelper.SetParameter("@Daily", Math.Round((TotalPayment), 2)),
                                                    DBhelper.SetParameter("@Balance", Math.Round(0.00, 2))
                                                    };
                                                    sqlQuery = "insert into am_paymentschedule(LoanID,date,amount,Balance) values (@LoanID,STR_TO_DATE(@Loandate,'%d/%m/%Y'),@Daily,@Balance)";
                                                    DBhelper.ExecuteNonQuery(sqlQuery, para);
                                                }
                                            }
                                            else
                                            {
                                                i = i - 1;
                                            }
                                            FirstPaymentDue = FirstPaymentDue.AddDays(1);
                                        }
                                        intReturnValue = 1;
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static DataTable DisplayPaymentSchedule(int LoanID, string Para_Purpose, string Para_Product, string Para_Type, string Para_DrawDownDate, string Para_LoanTerm, string Para_FirstPaymentDue, string Para_PaymetFrequecy, string Para_LowerRate, string Para_InterestMethod, string Para_LoanAmount)
        {
            /* for   payment schedule */
            DateTime FirstPaymentDue, FirstPaymentDue1, DrawDownDate;
            string PaymentPeriod = string.Empty, sqlQuery = string.Empty, InterestMethod = string.Empty, Getalldate = string.Empty, PayNumber = string.Empty, Facility = string.Empty, RepaymentType = string.Empty, sqlQuery1 = string.Empty;
            decimal RepaymentAmount, LoanAmount, DailyPayment, TotalPayment, DailyInterest, TotalInterest, LowerRate, DailyRate = 0;
            int Days;
            decimal LoanTerm;

            //Create DataTable
            DataTable table = new DataTable();
            table.Columns.Add("LoanID", typeof(int));
            table.Columns.Add("date", typeof(DateTime));
            table.Columns.Add("day", typeof(string));
            table.Columns.Add("amount", typeof(decimal));
            table.Columns.Add("Balance", typeof(decimal));
            //End Create DataTable

            try
            {
                Facility = Para_Product;
                RepaymentType = Para_Type;
                InterestMethod = Para_InterestMethod;
                if (Para_LoanAmount != "")
                {
                    //Parameeter varialbes
                    LoanAmount = Convert.ToDecimal(Para_LoanAmount);
                    FirstPaymentDue = Convert.ToDateTime(Para_FirstPaymentDue);
                    FirstPaymentDue1 = Convert.ToDateTime(Para_FirstPaymentDue);
                    DrawDownDate = Convert.ToDateTime(Para_DrawDownDate);
                    PaymentPeriod = Para_PaymetFrequecy;
                    LoanTerm = Convert.ToInt32(Para_LoanTerm);
                    LowerRate = Convert.ToDecimal(Para_LowerRate);
                    RepaymentAmount = LoanAmount / LoanTerm;

                    #region Simple
                    if (InterestMethod == "Simple")
                    {
                        if (Facility == "Unsecured Business Loan")
                        {
                            if (RepaymentType == "P And I")
                            {
                                if (PaymentPeriod == "Weekly")
                                {
                                    DailyRate = ((LowerRate / 100) / 365);
                                    DailyInterest = (LoanAmount * DailyRate);
                                    Days = Convert.ToInt32(LoanTerm * 7);
                                    TotalInterest = DailyInterest * Days;
                                    TotalPayment = LoanAmount + TotalInterest;
                                    DailyPayment = TotalPayment / LoanTerm;

                                    for (int i = 0; i <= LoanTerm; i++)
                                    {
                                        if (LoanAmount > DailyPayment)
                                        {
                                            LoanAmount = (LoanAmount - DailyPayment);
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
                                        }
                                        else
                                        {
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
                                            break;
                                        }
                                        FirstPaymentDue = FirstPaymentDue.AddDays(7);
                                    }
                                }
                                if (PaymentPeriod == "Fortnightly")
                                {
                                    DailyRate = ((LowerRate / 100) / 365);
                                    DailyInterest = (LoanAmount * DailyRate);
                                    Days = Convert.ToInt32(LoanTerm * 14);
                                    TotalInterest = DailyInterest * Days;
                                    TotalPayment = LoanAmount + TotalInterest;
                                    DailyPayment = TotalPayment / LoanTerm;

                                    for (int i = 0; i <= LoanTerm; i++)
                                    {
                                        if (LoanAmount > DailyPayment)
                                        {
                                            LoanAmount = (LoanAmount - DailyPayment);
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
                                        }
                                        else
                                        {
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
                                            break;
                                        }
                                        FirstPaymentDue = FirstPaymentDue.AddDays(14);
                                    }
                                }
                                if (PaymentPeriod == "Monthly")
                                {
                                    DailyRate = ((LowerRate / 100) / 365);
                                    DailyInterest = (LoanAmount * DailyRate);
                                    Days = Convert.ToInt32(LoanTerm * 30);
                                    TotalInterest = DailyInterest * Days;
                                    TotalPayment = LoanAmount + TotalInterest;
                                    DailyPayment = TotalPayment / LoanTerm;

                                    for (int i = 0; i <= LoanTerm; i++)
                                    {
                                        if (LoanAmount > DailyPayment)
                                        {
                                            LoanAmount = (LoanAmount - DailyPayment);
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
                                        }
                                        else
                                        {
                                            table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
                                            break;
                                        }
                                        FirstPaymentDue = FirstPaymentDue.AddDays(30);
                                    }
                                }
                                if (PaymentPeriod == "Daily(Mon-Fri)")
                                {
                                    DailyRate = ((LowerRate / 100) / 365);
                                    DailyInterest = (LoanAmount * DailyRate);
                                    Days = Convert.ToInt32(LoanTerm * 1);
                                    TotalInterest = DailyInterest * Days;
                                    TotalPayment = LoanAmount + TotalInterest;
                                    DailyPayment = TotalPayment / LoanTerm;

                                    for (int i = 0; i <= LoanTerm; i++)
                                    {
                                        if (FirstPaymentDue.DayOfWeek.ToString() != "Saturday" && FirstPaymentDue.DayOfWeek.ToString() != "Sunday")
                                        {
                                            if (LoanAmount > DailyPayment)
                                            {
                                                LoanAmount = (LoanAmount - DailyPayment);
                                                table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((DailyPayment), 2)), (Math.Round(LoanAmount, 2)));
                                            }
                                            else
                                            {
                                                table.Rows.Add(LoanID, FirstPaymentDue.ToShortDateString(), FirstPaymentDue.DayOfWeek.ToString(), (Math.Round((LoanAmount), 2)), (Math.Round(0.00, 2)));
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            i = i - 1;
                                        }
                                        FirstPaymentDue = FirstPaymentDue.AddDays(1);
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        public static DataTable CalculateRepayment(int LoanID, string Purpose, string Product, string Type, string DrawDownDate, string LoanTerm, string FirstPaymentDue, string PaymetFrequecy, string LowerRate, string InterestMethod, string LoanAmount)
        {
            decimal DailyRate = 0, LowerRate1, DailyInterest, LoanAmount1, LoanTerm1, TotalInterest, TotalPayment, DailyPayment;
            int Days;
            LowerRate1 = Convert.ToDecimal(LowerRate);
            LoanAmount1 = Convert.ToDecimal(LoanAmount);
            LoanTerm1 = Convert.ToInt32(LoanTerm);

            //Create DataTable
            DataTable table = new DataTable();
            table.Columns.Add("amount", typeof(decimal));
            table.Columns.Add("PaymetFrequecy", typeof(string));
            //End Create DataTable
            try
            {
                #region Simple
                if (InterestMethod == "Simple")
                {
                    if (Product == "Unsecured Business Loan")
                    {
                        if (Type == "P And I")
                        {
                            if (PaymetFrequecy == "Weekly")
                            {
                                DailyRate = ((LowerRate1 / 100) / 365);
                                DailyInterest = (LoanAmount1 * DailyRate);
                                Days = Convert.ToInt32(LoanTerm1 * 7);
                                TotalInterest = DailyInterest * Days;
                                TotalPayment = LoanAmount1 + TotalInterest;
                                DailyPayment = TotalPayment / LoanTerm1;
                                table.Rows.Add((Math.Round((DailyPayment), 2)), "Weekly");
                            }
                            else if (PaymetFrequecy == "Fortnightly")
                            {
                                DailyRate = ((LowerRate1 / 100) / 365);
                                DailyInterest = (LoanAmount1 * DailyRate);
                                Days = Convert.ToInt32(LoanTerm1 * 14);
                                TotalInterest = DailyInterest * Days;
                                TotalPayment = LoanAmount1 + TotalInterest;
                                DailyPayment = TotalPayment / LoanTerm1;
                                table.Rows.Add((Math.Round((DailyPayment), 2)), "Fortnightly");
                            }
                            else if (PaymetFrequecy == "Monthly")
                            {
                                DailyRate = ((LowerRate1 / 100) / 365);
                                DailyInterest = (LoanAmount1 * DailyRate);
                                Days = Convert.ToInt32(LoanTerm1 * 30);
                                TotalInterest = DailyInterest * Days;
                                TotalPayment = LoanAmount1 + TotalInterest;
                                DailyPayment = TotalPayment / LoanTerm1;
                                table.Rows.Add((Math.Round((DailyPayment), 2)), "Monthly");
                            }
                            else if (PaymetFrequecy == "Daily(Mon-Fri)")
                            {
                                DailyRate = ((LowerRate1 / 100) / 365);
                                DailyInterest = (LoanAmount1 * DailyRate);
                                Days = Convert.ToInt32(LoanTerm1 * 1);
                                TotalInterest = DailyInterest * Days;
                                TotalPayment = LoanAmount1 + TotalInterest;
                                DailyPayment = TotalPayment / LoanTerm1;
                                table.Rows.Add((Math.Round((DailyPayment), 2)), "Daily");
                            }
                        }
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        public static int UpdateLoanInfoFromAsessment(string app_id, string LoanNumber, string Lender, string EditDate, string LastResult, string Memo)
        {
            int retvalu = 0;
            int loanid = 0;
            string EffectiveDate;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };
                string query = "select effectivedate from am_loan1 where app_id=@app_id";
                var dtdata = DBhelper.ExecuteDataset(query, para1).Tables[0];

                EffectiveDate = dtdata.Rows[0]["effectivedate"].ToString();
                if (EffectiveDate == null || EffectiveDate == string.Empty)
                {
                    var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@LoanNumber", LoanNumber),
                    DBhelper.SetParameter("@Lender", Lender),
                    DBhelper.SetParameter("@EditDate", EditDate),
                    DBhelper.SetParameter("@LastResult", LastResult),
                    DBhelper.SetParameter("@Memo", Memo)
                    };
                    query = "update am_loan1 set LoanNumber=@LoanNumber,Lender=@Lender,EditDate=STR_TO_DATE(@EditDate,'%m/%d/%Y'),LastResult=@LastResult,Memo=@Memo where app_id=@app_id;SELECT loanid FROM `am_loan1` where app_id=@app_id";
                    DataTable dt = DBhelper.ExecuteDataset(query, para).Tables[0];
                    if (dt.Rows.Count > 0)
                        loanid = Convert.ToInt32(dt.Rows[0]["LoanID"]);

                    retvalu = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int UpdateLoanInfo(string LoanID, string LoanNumber, string Lender, string Purpose, string Product, string Type, string DrawDownDate, string LoanTerm, string FirstPaymentDue, string PaymetFrequecy, string LowerRate, string HigherRate, string InterestMethod, string LoanAmount, string DishonorFee, string DefaultNotice, string EditDate)
        {
            int retvalu = 0;
            int loanid = 0;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                };
                string query = "select effectivedate from am_loan1 where LoanID=@LoanID";
                var dtdata = DBhelper.ExecuteDataset(query, para1).Tables[0];

                string EffectiveDate = dtdata.Rows[0]["effectivedate"].ToString();
                if (EffectiveDate == null || EffectiveDate == string.Empty)
                {
                    if (DrawDownDate == "")
                        DrawDownDate = null;
                    if (FirstPaymentDue == "")
                        FirstPaymentDue = null;
                    if (PaymetFrequecy == "")
                        PaymetFrequecy = null;

                    var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@LoanNumber", LoanNumber),
                    DBhelper.SetParameter("@Lender", Lender),
                    DBhelper.SetParameter("@Purpose", Purpose),
                    DBhelper.SetParameter("@Product", Product),
                    DBhelper.SetParameter("@Type", Type),
                    DBhelper.SetParameter("@DrawDownDate", DrawDownDate),
                    DBhelper.SetParameter("@LoanTerm", LoanTerm),
                    DBhelper.SetParameter("@FirstPaymentDue", FirstPaymentDue),
                    DBhelper.SetParameter("@PaymetFrequecy", PaymetFrequecy),
                    DBhelper.SetParameter("@LowerRate", LowerRate),
                    DBhelper.SetParameter("@HigherRate", HigherRate),
                    DBhelper.SetParameter("@InterestMethod", InterestMethod),
                    DBhelper.SetParameter("@LoanAmount", LoanAmount),
                    DBhelper.SetParameter("@DishonorFee", DishonorFee),
                    DBhelper.SetParameter("@DefaultNotice", DefaultNotice),
                    DBhelper.SetParameter("@EditDate", EditDate)
                    };

                    query = "update am_loan1 set LoanNumber=@LoanNumber,PaymentPeriod=@PaymetFrequecy,DrawDownDate=STR_TO_DATE(@DrawDownDate,'%m/%d/%Y'),LoanTerm=@LoanTerm,FirstPaymentDue=STR_TO_DATE(@FirstPaymentDue,'%m/%d/%Y'),LowerRate=@LowerRate,HigherRate=@HigherRate,InterestMethod=@InterestMethod,Lender=@Lender,Product=@Product,Type=@Type,Purpose=@Purpose,LoanAmount=@LoanAmount,DishonorFee=@DishonorFee,DefaultNotice=@DefaultNotice,EditDate=STR_TO_DATE(@EditDate,'%m/%d/%Y') where LoanID=@LoanID;SELECT loanid FROM `am_loan1` where LoanID=@LoanID";
                    DataTable dt = DBhelper.ExecuteDataset(query, para).Tables[0];
                    if (dt.Rows.Count > 0)
                        loanid = Convert.ToInt32(dt.Rows[0]["LoanID"]);
                    
                    retvalu = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int UpdateLoanInfo(string LoanID, string Memo, string EditDate)
        {
            int retvalu = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@Memo", Memo),
                    DBhelper.SetParameter("@EditDate", EditDate)
                };

                string sqlUpdateLoanQuery = "update am_loan1 set Memo=@Memo,EditDate=STR_TO_DATE(@EditDate,'%m/%d/%Y') where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateLoanQuery, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int AddBankInfoFromAsessment(int LoanID, string LoanType)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID), 
                    DBhelper.SetParameter("@LoanType", LoanType)
                };

                string str = "insert into am_bankdetails(LoanID,LoanType) values(@LoanID,@LoanType)";
                DBhelper.ExecuteNonQuery(str, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int UpdateBankInfo(string BankName, string AccountNo, string BSB, int LoanID, string LoanType, string AccountHolder)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BankName", BankName),
                    DBhelper.SetParameter("@AccountNo", AccountNo),
                    DBhelper.SetParameter("@BSB", BSB),
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@LoanType", LoanType),
                    DBhelper.SetParameter("@AccountHolder", AccountHolder)
                };

                string query = "select bankid from am_bankdetails where LoanID=@LoanID";
                DataSet ds = DBhelper.ExecuteDataset(query, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    query = "update am_bankdetails set bankname=@BankName,bsb=@BSB,accountno=@AccountNo,HolderName=@AccountHolder where LoanID=@LoanID and LoanType=@LoanType";
                    DBhelper.ExecuteNonQuery(query, para);
                    intReturnValue = 1;
                }
                else
                {
                    query = "insert into am_bankdetails(bankname,branchname,bsb,accname,accountno,SBI,LoanID,LoanType,HolderName) values(@BankName,null,@BSB,null,@AccountNo,null,@LoanID,@LoanType,@AccountHolder)";
                    DBhelper.ExecuteNonQuery(query, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /*Methods for effective contract*/
        public static DataSet UpdateDDRAccount(string NewDDRDate, string LoanID, string LastName, string FirstName, string Address1, string Suburb, string State, string PostCode, string Email, string MobilePhone)
        {
            DataSet ds = null;
            try
            {
                if (NewDDRDate == "")
                    NewDDRDate = null;
                string[] DDRDate = NewDDRDate.Split('/');
                string ContractStartDate = (DDRDate[2] + "-" + DDRDate[0] + "-" + DDRDate[1]);
                string digitalKey = WebConfigurationManager.AppSettings["EziDebitDigitalKey"].ToString();
                int CustomerRef = CRMAdapter.PayByEzidebit.AddCustomersFromContractPage(digitalKey, "", "", "Patel", "Jignesh", Address1, "", Suburb, State, PostCode, Email, MobilePhone, ContractStartDate, "NO", "NO", "NO", "");
                //int CustomerRef = CRMAdapter.PayByEzidebit.AddCustomersFromContractPage(digitalKey, "", "", LastName, FirstName, Address1, "", Suburb, State, PostCode, Email, MobilePhone, ContractStartDate, "NO", "NO", "NO", "");

                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@NewDDRDate", NewDDRDate),
                    DBhelper.SetParameter("@CustomerRef", CustomerRef)
                };

                string query = "update am_loan1 set NewDDRDate=STR_TO_DATE(@NewDDRDate,'%m/%d/%Y'),EzidebitRefNo=@CustomerRef where LoanID=@LoanID;select EzidebitRefNo,NewDDRDate from am_loan1 where LoanID=@LoanID";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet UpdateBankAccDate(string BankAccDate, string LoanID)
        {
            DataSet ds = null;
            try
            {
                if (BankAccDate == "")
                    BankAccDate = null;

                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@BankAccDate", BankAccDate),
                };

                string query = "update am_loan1 set BankAccDate=STR_TO_DATE(@BankAccDate,'%m/%d/%Y') where LoanID=@LoanID;select BankAccDate from am_loan1 where LoanID=@LoanID";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet UpdatePaymentSchDate(string PaymentSchDate, string LoanID, string CustomerRefNo)
        {
            DataSet ds = null;
            try
            {
                if (PaymentSchDate == "")
                    PaymentSchDate = null;

                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@PaymentSchDate", PaymentSchDate),
                };

                string sqlQuery = "update am_loan1 set PaymentSchDate=STR_TO_DATE(@PaymentSchDate,'%m/%d/%Y') where LoanID=@LoanID;select PaymentSchDate from am_loan1 where LoanID=@LoanID";
                ds = DBhelper.ExecuteDataset(sqlQuery, para);

                string digitalKey = WebConfigurationManager.AppSettings["EziDebitDigitalKey"].ToString();
                sqlQuery = "select * from am_paymentschedule where loanid=@LoanID";
                DataTable dt = DBhelper.ExecuteDataset(sqlQuery, para).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    sqlQuery = "update am_paymentschedule set PaymentRefNo=" + dr["paymentscheduleid"] + " where paymentscheduleid=" + dr["paymentscheduleid"];
                    DBhelper.ExecuteNonQuery(sqlQuery);
                    CRMAdapter.PayByEzidebit.AddPaymentsFromContract(digitalKey, CustomerRefNo, "", Convert.ToDateTime(dr["date"].ToString()).ToString("yyyy-MM-dd"), Convert.ToInt64(decimal.Parse(dr["amount"].ToString())), dr["paymentscheduleid"].ToString(), "");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static int UpdateEffectiveDate(string EffectiveDate, string LoanID, string DoubleChecked, string LoanStatus)
        {
            int retvalu = 0;
            try
            {
                if (EffectiveDate == "")
                    EffectiveDate = null;

                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@EffectiveDate", EffectiveDate),
                    DBhelper.SetParameter("@DoubleChecked", DoubleChecked),
                    DBhelper.SetParameter("@LoanStatus", LoanStatus)
                };

                string sqlUpdateLoanQuery = "update am_loan1 set EffectiveDate=STR_TO_DATE(@EffectiveDate,'%m/%d/%Y'),DoubleChecked=@DoubleChecked,LoanStatusID=@LoanStatus where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateLoanQuery, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        /* this statement is show loan detail in gridview */
        public static DataSet GetLoanDetails(int pageIndex, int pageSize, string searchtext, string conditiontext, string sortText)
        {
            DataSet ds;
            try
            {
                if (!string.IsNullOrEmpty(searchtext))
                {
                    var sbWhere = new StringBuilder(" WHERE ")
                        .AppendFormat("Lender like '%{0}%'", searchtext)
                        .AppendFormat(" OR Product like '%{0}%'", searchtext)
                        .AppendFormat(" OR Purpose like '%{0}%'", searchtext)
                        .Append(conditiontext);
                    var sbSql = new StringBuilder("select IFNULL(DrawDownDate,'') as DrawDownDate,CreateDate,EditDate,LoanNumber,Lender,LoanID,LoanTerm,Product,Purpose,IFNULL(FirstPaymentDue,'') as FirstPaymentDue,(select count(*) from am_loan1");
                    sbSql.Append(sbWhere);
                    sbSql.Append(") as Total from am_loan1");
                    sbSql.Append(sbWhere);
                    if (string.IsNullOrEmpty(sortText))
                        sbSql.Append(" order by EditDate");
                    else
                        sbSql.Append(" ORDER BY " + sortText);
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
                else
                {
                    conditiontext = conditiontext.Replace(" and", " where");
                    var sbSql = new StringBuilder("select IFNULL(DrawDownDate,'') as DrawDownDate,CreateDate,EditDate,LoanNumber,Lender,LoanID,LoanTerm,Product,Purpose,IFNULL(FirstPaymentDue,'') as FirstPaymentDue,(select count(*) from am_loan1")
                    .Append(conditiontext)
                    .Append(" ) as Total from am_loan1")
                    .Append(conditiontext);
                    if (string.IsNullOrEmpty(sortText))
                        sbSql.Append(" order by EditDate Desc");
                    else
                        sbSql.Append(" ORDER BY " + sortText);

                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return ds;
        }

        public static DataTable SearchLoanDetailByAppID(int app_id)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };
                dt = DBhelper.ExecuteDataset("select * from am_loan1 where app_id=@app_id", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchBankDetailByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                };
                dt = DBhelper.ExecuteDataset("select * from am_bankdetails where loanid=@LoanID", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchLoanDetailByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                };
                dt = DBhelper.ExecuteDataset("select * from am_loan1 where loanid=@LoanID", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /* This function will check the LenderStatusID is exist or not to invoke AddLoan or SaveLoan function*/
        public static DataTable CheckLenderStatus(int app_id)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };
                dt = DBhelper.ExecuteDataset("select LoanStatusID from am_loan1 where app_id=@app_id", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetSingleAccountidAppid(int account_id)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@account_id", account_id)
                };
                dt = DBhelper.ExecuteDataset("select account_id,app_id from ams_loanaccount where account_id=@account_id", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
    }
}
